# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
uuid    = require 'uuid'
moment  = require 'moment'
fs      = require 'fs'
multer  = require 'multer'
mmm     = require 'mmmagic'
mkdirp  = require 'mkdirp'
jobs    = require '../lib/merch_jobs'
router = express.Router()
module.exports = router


# 下面这些字段不能在数据表中存入空字符串(外键或格式要求)
router.use (req, res, next) ->
    for k in [
        't20_fee', 'trctl', 'settl_merid', 'mapto_merid',
        'unit_license_exp', 'input_time', 'check_time', 'effect_time'
    ]
        if not req.body[k] or req.body[k].length == 0
            req.body[k] = null
    next()


router.all '/', (req, res, next) ->
    where_clause = "(
        m.serial::text like $1 or m.uuid like $1 or
        m.merno like $1 or m.name like $1 or m.contact like $1 or
        m.telephone like $1 or m.mobile like $1 or m.fax like $1 or
        m.email like $1 or m.address like $1 or
        m.unit_name like $1 or m.unit_code like $1 or m.unit_license like $1 or
        m.unit_tax_code like $1 or m.unit_address like $1 or
        m.unit_legal_name like $1 or m.unit_cred_no like $1
    )"
    # 通过平台/外联过滤
    if req.body.inside and req.body.inside.length > 0
        where_clause += " and m.inside::boolean = #{req.body.inside == 'true'}"
        res.locals.inside = req.body.inside

    # 通过渠道过滤
    if req.body.chnl and req.body.chnl.length > 0
        where_clause += " and m.chnl::int = #{req.body.chnl}"
        res.locals.chnl = req.body.chnl

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询商户表中的记录总数
    await client.query \
        "select count(m.*)::int as count from pay_merch as m
            where #{where_clause} and m.shaded = false", [
            '%' + res.locals.keyword + '%'
        ], defer err, result
    if err then done(client); return next(err)
    res.locals.total = result.rows[0].count

    # 非正常商户数量，计算偏移时需要使用
    ex_count = 0

    # 查询待复核商户表中当前用户录入的记录
    await client.query \
        "select count(m.*)::int as count from pay_merch_ex as m
            where #{where_clause} and m.inputer = $2", [
            '%' + res.locals.keyword + '%'
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)
    ex_count += result.rows[0].count

    # 如果用户具有商户复核权限，那么查询待复核的记录
    if res.locals.page_perms.check
        await client.query \
            "select count(m.*)::int as count from pay_merch_ex as m
                where #{where_clause} and m.inputer <> $2 and
                    m.checked = false and m.rejected = false", [
                '%' + res.locals.keyword + '%'
                req.session.userinfo.uuid
            ], defer err, result
        if err then done(client); return next(err)
        ex_count += result.rows[0].count

    # 由当前用户复核后尚未生效的记录
    if res.locals.page_perms.check
        await client.query \
            "select count(m.*)::int as count from pay_merch_ex as m
                where #{where_clause} and m.checker = $2 and
                    m.checked = true", [
                '%' + res.locals.keyword + '%'
                req.session.userinfo.uuid
            ], defer err, result
        if err then done(client); return next(err)
        ex_count += parseInt(result.rows[0].count)

    res.locals.total += ex_count
    ret_records = []

    # 首先查询待复核的记录，置顶，只在第一页显示复核表中的信息
    if res.locals.offset == 0 and res.locals.page_perms.check
        await client.query \
            "select m.*, u.realname as input_user,
                    m2.name as settl_mer_name, m2.merno as settl_mer_no,
                    m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
                    f1.name as t20_fee_name, f1.notes as t20_fee_notes,
                    true as ex
                from pay_merch_ex as m
                left join web_users as u on u.uuid = m.inputer
                left join pay_merch as m2 on m.settl_merid = m2.uuid
                left join pay_merch as m3 on m.mapto_merid = m3.uuid
                left join pay_fee as f1 on f1.uuid = m.t20_fee
                where #{where_clause} and m.inputer <> $2 and
                    m.checked = false and m.rejected = false
                order by m.serial desc limit $3::int", [
                '%' + res.locals.keyword + '%'
                req.session.userinfo.uuid
                res.locals.page_rows - ret_records.length
            ], defer err, result
        if err then done(client); return next(err)
        ret_records.push result.rows...

    # 如果查询的结果集还不足一页，则继续查找复核后未生效的记录
    if (
        res.locals.page_perms.check and res.locals.offset == 0 and
        ret_records.length < res.locals.page_rows)
        await client.query \
            "select m.*, u.realname as input_user,
                    u2.realname as check_user,
                    m2.name as settl_mer_name, m2.merno as settl_mer_no,
                    m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
                    f1.name as t20_fee_name, f1.notes as t20_fee_notes,
                true as ex
                from pay_merch_ex as m
                left join web_users as u on u.uuid = m.inputer
                left join web_users as u2 on u2.uuid = m.checker
                left join pay_merch as m2 on m.settl_merid = m2.uuid
                left join pay_merch as m3 on m.mapto_merid = m3.uuid
                left join pay_fee as f1 on f1.uuid = m.t20_fee
                where #{where_clause} and m.checker = $2 and m.checked = true
                order by m.serial desc limit $3::int", [
                '%' + res.locals.keyword + '%'
                req.session.userinfo.uuid
                res.locals.page_rows - ret_records.length
            ], defer err, result
        if err then done(client); return next(err)
        ret_records.push result.rows...

    # 如果查询的结果集还不足一页，则继续查找用户录入或修改的记录
    if res.locals.offset == 0 and ret_records.length < res.locals.page_rows
        await client.query \
            "select m.*, u.realname as input_user,
                    u2.realname as check_user,
                    m2.name as settl_mer_name, m2.merno as settl_mer_no,
                    m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
                    f1.name as t20_fee_name, f1.notes as t20_fee_notes,
                true as ex
                from pay_merch_ex as m
                left join web_users as u on u.uuid = m.inputer
                left join web_users as u2 on u2.uuid = m.checker
                left join pay_merch as m2 on m.settl_merid = m2.uuid
                left join pay_merch as m3 on m.mapto_merid = m3.uuid
                left join pay_fee as f1 on f1.uuid = m.t20_fee
                where #{where_clause} and m.inputer = $2
                order by m.serial desc limit $3::int", [
                '%' + res.locals.keyword + '%'
                req.session.userinfo.uuid
                res.locals.page_rows - ret_records.length
            ], defer err, result
        if err then done(client); return next(err)
        ret_records.push result.rows...

    # 如果查询的结果集还不足一页，则继续查找正常的商户记录
    if ret_records.length < res.locals.page_rows
        await client.query \
            "select m.*, u.realname as input_user,
                    u2.realname as check_user,
                    m2.name as settl_mer_name, m2.merno as settl_mer_no,
                    m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
                    f1.name as t20_fee_name, f1.notes as t20_fee_notes,
                    count(distinct m4.uuid) + count(distinct m5.uuid) +
                    count(distinct t.uuid) as n_refs,
                    count(t.*) as n_terms
                from pay_merch as m
                left join web_users as u on u.uuid = m.inputer
                left join web_users as u2 on u2.uuid = m.checker
                left join pay_merch as m2 on m.settl_merid = m2.uuid
                left join pay_merch as m3 on m.mapto_merid = m3.uuid
                left join pay_fee as f1 on f1.uuid = m.t20_fee
                left join pay_merch as m4 on (
                    m4.settl_merid = m.uuid or m4.mapto_merid = m.uuid)
                left join pay_merch_ex as m5 on (
                    m5.settl_merid = m.uuid or m5.mapto_merid = m.uuid)
                left join pay_terms as t on t.merid = m.uuid
                where #{ where_clause } and m.shaded = false
                group by m.serial, m.uuid, u.realname, u2.realname,
                    m2.name, m2.merno, m3.name, m3.merno, f1.name, f1.notes
                order by m.serial desc
                offset $2::int limit $3::int", [
                    '%' + res.locals.keyword + '%'
                    if res.locals.offset == 0 then 0 else res.locals.offset - ex_count
                    res.locals.page_rows - ret_records.length
                ], defer err, result
        if err then done(client); return next(err)
        ret_records.push result.rows...

    done()
    res.render 'merch/index', records: ret_records


router.all '/settl', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '虚拟清算户'

    where_clause = "1 = 1"
    params = []

    res.locals.settl_merid = req.query.settl_merid
    if not res.locals.settl_merid
        res.locals.settl_merid = req.body.settl_merid
    if res.locals.settl_merid
        where_clause += ' and m.settl_merid = $1'
        params.push res.locals.settl_merid

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询商户表中的记录总数
    await client.query \
        "select count(m.*) as count from pay_merch as m
            where #{where_clause}", params, defer err, result
    if err then done(client); return next(err)
    res.locals.total = parseInt(result.rows[0].count)

    offset_index = params.length + 1
    params.push res.locals.offset
    limit_index = params.length + 1
    params.push res.locals.page_rows
    await client.query \
        "select m.* from pay_merch as m where #{ where_clause }
            order by serial desc
            offset $#{offset_index}::int
            limit $#{limit_index}::int",
        params, defer err, result
    if err then done(client); return next(err)
    records = result.rows

    # 查询清算商户列表
    await client.query \
        "select * from pay_merch where chnl = 0", defer err, result
    if err then done(client); return next(err)
    res.locals.settl_merids = result.rows
    done()
    res.render 'merch/settl', records: records


body_dropdown_items = (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询虚拟清算户
    await client.query \
        "select uuid, merno, name from pay_merch where
            inside = true and chnl = 0 and
            disabled = false and shaded = false", defer err, result
    if err then done(client); return next(err)
    res.locals.settl_merids = result.rows

    # 查询交易控制模板
    await client.query "select * from pay_trctl_t", defer err, result
    if err then done(client); return next(err)
    res.locals.trctl_list = result.rows

    # 查询手续费列表
    await client.query \
        "select * from pay_fee", defer err, result
    if err then done(client); return next(err)
    res.locals.fees = result.rows
    done()
    next()

router.get '/new', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '新增商户'
    res.render 'merch/new', record: {}


router.post '/new', (req, res, next) ->
    for k in ['inside', 'chnl', 'merno', 'name']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    # 去除商户号空白字符
    req.body.merno = req.body.merno.replace(/\s/g, '')

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询商户是否已经存在
    await client.query "select * from pay_merch
        where inside = $1 and chnl = $2 and merno = $3", [
            req.body.inside, req.body.chnl, req.body.merno
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next(new Error('商户 ' + req.body.merno + ' 已存在，请检查'))

    # 查询待复核表中是否有此商户信息
    await client.query "select m.*, u.realname as user
        from pay_merch_ex as m
        left join web_users as u on u.uuid = m.inputer
        where m.inside = $1 and m.chnl = $2 and
            m.merno = $3 and m.action = 1", [
            req.body.inside, req.body.chnl, req.body.merno
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next(new Error('商户' + req.body.merno + ' 已被用户 ' +
            result.rows[0].user + ' 添加，尚未复核或尚未生效'))

    # 在商户信息表中添加一条影子记录，用于终端可以引用未复核的商户
    merch_uuid = uuid.v4()
    await client.query "insert into pay_merch (
            shaded, uuid, inside, chnl, merno, name, trctl, inputer, checker
        ) values (
            true, $1, $2, $3, $4, $5, $6, $7, $8
        ) returning serial", [
            merch_uuid, req.body.inside is 'true',
            parseInt(req.body.chnl), req.body.merno, req.body.name,
            req.body.trctl,
            req.session.userinfo.uuid, req.session.userinfo.uuid,
        ], defer err, result
    if err then done(client); return next(err)
    merch_serial = result.rows[0].serial

    # 添加商户信息到待复核表
    await client.query "insert into pay_merch_ex (
            uuid,              inside,         chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            ref_serial,        ref_uuid,
            inputer,           input_time,     action
        ) values (
            $1, $2, $3, $4, $5, $6, $7, $8,
            $9, $10, $11, $12, $13, $14, $15,
            $16, $17, $18, $19, $20, $21, $22, $23,
            $24, $25, $26, $27, $28, $29, $30, $31,
            $32, $33,
            $34, $35,
            $36, CURRENT_TIMESTAMP, 1
        )", [
            uuid.v4(), req.body.inside is 'true',
            parseInt(req.body.chnl), req.body.merno,
            req.body.t20_fee, req.body.trctl,
            req.body.settl_merid, req.body.mapto_merid,
            req.body.name, req.body.contact,
            req.body.telephone, req.body.mobile,
            req.body.fax, req.body.email, req.body.address,
            req.body.unit_name, req.body.unit_name_abbr,
            req.body.unit_english, req.body.unit_english_abbr,
            req.body.unit_code, req.body.unit_license,
            req.body.unit_license_exp, req.body.unit_tax_code,
            req.body.unit_issuer, req.body.unit_address,
            parseInt(req.body.unit_type) or 99, req.body.unit_legal_name,
            parseInt(req.body.unit_cred_type) or 99, req.body.unit_cred_no,
            parseInt(req.body.unit_assets) or 0, req.body.unit_scope,
            req.body.keys, req.body.notes,
            merch_serial, merch_uuid,
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 增加商户信息可能影响到所有用户的通知
    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/merch'


router.get '/edit/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '编辑商户信息'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_merch
        where serial = $1 or uuid = $2", [
            parseInt(req.params.id) or 0, req.params.id
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next new Error('未查询到商户记录')

    record = result.rows[0]
    res.render 'merch/edit', record: record


router.post '/edit', (req, res, next) ->
    for k in ['serial', 'uuid', 'inside', 'chnl', 'merno', 'name']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    # 去除商户号空白字符
    req.body.merno = req.body.merno.replace(/\s/g, '')

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select m.*, u.realname as input_user
            from pay_merch_ex as m
            left join web_users as u on m.inputer = u.uuid
            where m.ref_uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("该记录被用户 [#{result.rows[0].input_user}]
            修改后尚未生效，在新信息生效前不允许再次修改")

    await client.query "insert into pay_merch_ex (
            uuid,              inside,         chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            ref_serial,        ref_uuid,
            inputer,           input_time,     action
        ) values (
            $1, $2, $3, $4, $5, $6, $7, $8,
            $9, $10, $11, $12, $13, $14, $15,
            $16, $17, $18, $19, $20, $21, $22, $23,
            $24, $25, $26, $27, $28, $29, $30, $31,
            $32, $33,
            $34, $35,
            $36, CURRENT_TIMESTAMP, 2
        )", [
            uuid.v4(), req.body.inside is 'true',
            req.body.chnl, req.body.merno,
            req.body.t20_fee, req.body.trctl,
            req.body.settl_merid, req.body.mapto_merid,
            req.body.name, req.body.contact,
            req.body.telephone, req.body.mobile,
            req.body.fax, req.body.email, req.body.address,
            req.body.unit_name, req.body.unit_name_abbr,
            req.body.unit_english, req.body.unit_english_abbr,
            req.body.unit_code, req.body.unit_license,
            req.body.unit_license_exp, req.body.unit_tax_code,
            req.body.unit_issuer, req.body.unit_address,
            req.body.unit_type, req.body.unit_legal_name,
            parseInt(req.body.unit_cred_type) or 99, req.body.unit_cred_no,
            parseInt(req.body.unit_assets) or 0, req.body.unit_scope,
            req.body.keys, req.body.notes,
            req.body.serial, req.body.uuid,
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/merch'


router.post '/disable', (req, res, next) ->
    res.type('json')
    for k in ['serial', 'uuid', 'disabled']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "update pay_merch set disabled = $1
        where serial = $2 and uuid = $3", [
            req.body.disabled, req.body.serial, req.body.uuid,
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.json succ: true, redirect: '/merch'


router.post '/delete', (req, res, next) ->
    res.type('json')
    for k in ['serial', 'uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 如果有其它商户引用了此记录，则禁止删除
    await client.query \
        "select * from pay_merch where settl_merid = $1 or mapto_merid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("该记录正被商户 [#{result.rows[0].merno}]
            引用，在解除引用关系之前不允许删除")

    # 如果此记录存在待复核的条目，则禁止删除
    await client.query \
        "select m.*, u.realname as input_user
            from pay_merch_ex as m
            left join web_users as u on u.uuid = m.inputer
            where m.ref_uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("该记录被用户 [#{result.rows[0].input_user}]
            修改后尚未生效，在新信息生效前不允许删除")

    # 如果待复核表中有其它商户引用了此记录，则禁止删除
    await client.query \
        "select * from pay_merch_ex
            where settl_merid = $1 or mapto_merid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("该记录正被待复核的商户 [#{result.rows[0].merno}]
            引用，在解除引用关系之前不允许删除")

    await client.query "insert into pay_merch_ex (
            uuid,              inside,         chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            ref_serial,        ref_uuid,
            inputer,           input_time,     action
        ) ( select
            $1,                inside,         chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            serial,            uuid,
            $2,                CURRENT_TIMESTAMP, 3
            from pay_merch where uuid = $3
        )", [
            uuid.v4(), req.session.userinfo.uuid, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.get '/input/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '修改商户信息'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select m.*, u.realname as check_user
        from pay_merch_ex as m
        left join web_users as u on u.uuid = m.checker
        where (m.serial = $1 or m.uuid = $2) and m.inputer = $3", [
            parseInt(req.params.id) or 0,
            req.params.id,
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next new Error('未查询到商户记录，可能已经被复核并生效了.')

    record = result.rows[0]
    if record.checked
        return next new Error("""
            该记录已被``#{record.check_user}''复核，不允许继续修改""")

    res.render 'merch/input', record: record


router.post '/input/delete', (req, res, next) ->
    res.type('json')
    for k in ['serial', 'uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select * from pay_merch_ex where checked = false and
            inputer = $1 and serial = $2 and uuid = $3", [
            req.session.userinfo.uuid, req.body.serial, req.body.uuid,
        ], defer err, result
    if err then done(client); return next(err)
    if result.rows.length == 0
        done()
        return next new Error('未查询到待删除记录，可能已被删除.')

    remove_record = result.rows[0]

    client.query 'begin'
    await client.query \
        "delete from pay_merch_ex where checked = false and
            inputer = $1 and serial = $2 and uuid = $3", [
            req.session.userinfo.uuid, req.body.serial, req.body.uuid,
        ], defer err, result
    if err then done(client); return next(err)

    # 如果是删除的是新增未复核记录，则商户表中存在一条影子记录，需要删除
    if remove_record.action == 1
        await client.query \
            "delete from pay_merch where shaded = true and uuid = $1", [
                remove_record.ref_uuid
            ], defer err, result
        if err then done(client); return next(err)
    client.query 'commit'
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


input_precheck = (req, res, next) ->
    for k in ['serial', 'uuid', 'inside', 'chnl', 'merno', 'name',]
        if not req.body[k]
            return next new Error("请求缺少参数[#{k}]")

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 检查商户信息是否已被复核，复核后的信息不允许修改
    await client.query \
        "select m.checked, u.realname as check_user
            from pay_merch_ex as m
            left join web_users as u on u.uuid = m.checker
            where m.serial = $1 and m.uuid = $2", [
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next new Error("""
            未找到商户 #{req.body.uuid} 的信息，可能已被复核并已生效""")

    record = result.rows[0]
    if result.rows[0].checked
        return next new Error("""
            该商户信息已被用户 #{record.check_user} 复核，不允许继续修改""")
    next()


do_input_1 = (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "update pay_merch_ex set
            inside = $1,             chnl = $2,             merno = $3,
            trctl = $4,              settl_merid = $5,      mapto_merid = $6,
            name = $7,               contact = $8,          telephone = $9,
            mobile = $10,            fax = $11,             email = $12,
            address = $13,
            unit_name = $14,         unit_name_abbr = $15,  unit_english = $16,
            unit_english_abbr = $17, unit_code = $18,       unit_license = $19,
            unit_license_exp = $20,  unit_tax_code = $21,   unit_issuer = $22,
            unit_address = $23,      unit_type = $24,       unit_legal_name = $25,
            unit_cred_type = $26,    unit_cred_no = $27,    unit_assets = $28,
            unit_scope = $29,
            keys = $30,              notes = $31,
            inputer = $32,           input_time = CURRENT_TIMESTAMP,
            checked = false,         rejected = false,      t20_fee = $33
        where serial = $34 and uuid = $35", [
            req.body.inside is 'true', parseInt(req.body.chnl), req.body.merno,
            req.body.trctl, req.body.settl_merid, req.body.mapto_merid,
            req.body.name, req.body.contact, req.body.telephone, req.body.mobile,
            req.body.fax, req.body.email, req.body.address,
            req.body.unit_name, req.body.unit_name_abbr,
            req.body.unit_english, req.body.unit_english_abbr,
            req.body.unit_code, req.body.unit_license,
            req.body.unit_license_exp, req.body.unit_tax_code,
            req.body.unit_issuer, req.body.unit_address,
            req.body.unit_type, req.body.unit_legal_name,
            parseInt(req.body.unit_cred_type) or 99, req.body.unit_cred_no,
            parseInt(req.body.unit_assets) or 0, req.body.unit_scope,
            req.body.keys, req.body.notes,
            req.session.userinfo.uuid,
            req.body.t20_fee,
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/merch'


do_input_3 = (req, res, next) ->
    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "update pay_merch_ex set
            inputer = $1,            input_time = CURRENT_TIMESTAMP,
            checked = false,         rejected = false
        where serial = $2 and uuid = $3", [
            req.session.userinfo.uuid,
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.redirect '/merch'


router.post '/input/:action', input_precheck, (req, res, next) ->
    # 去除商户号空白字符
    req.body.merno = req.body.merno.replace(/\s/g, '')

    switch parseInt(req.params.action)
        when 1, 2 then return do_input_1 req, res, next
        when 3 then return do_input_3 req, res, next
        else return next new Error('您访问的功能不存在')


router.get '/check/1/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '确认新增商户'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 可以通过序号和 UUID 之一来查询
    key = if req.params.id.indexOf('-') > 0 then 'uuid' else 'serial'
    sql = "select m.*, u.realname as input_user, u2.realname as check_user
            from pay_merch_ex as m
            left join web_users as u on m.inputer = u.uuid
            left join web_users as u2 on m.checker = u2.uuid
            where m.#{key} = $1 and m.inputer <> $2 and action = 1"
    await client.query sql, [
        req.params.id, req.session.userinfo.uuid ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next(new Error('未找到商户记录'))
    record = result.rows[0]
    res.render 'merch/check1', record: record


router.get '/check/2/:id', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '确认变更商户信息'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select m.*,
            u.realname as input_user, u2.realname as check_user,
            m2.name as settl_mer_name, m2.merno as settl_mer_no,
            m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
            f1.name as t20_fee_name, f1.notes as t20_fee_notes
        from pay_merch_ex as m
        left join web_users as u on m.inputer = u.uuid
        left join web_users as u2 on m.checker = u2.uuid
        left join pay_merch as m2 on m.settl_merid = m2.uuid
        left join pay_merch as m3 on m.mapto_merid = m3.uuid
        left join pay_fee as f1 on f1.uuid = m.t20_fee
        where (m.serial = $1 or m.uuid = $2) and
            m.inputer <> $3 and action = 2", [
        parseInt(req.params.id) or 0, req.params.id,
        req.session.userinfo.uuid
    ], defer err, result
    if err then done(client); return next(err)

    if result.rows.length == 0
        done()
        return next(new Error('未找到商户记录'))

    record = result.rows[0]

    # 查询原商户信息
    await client.query "select m.*,
            m2.name as settl_mer_name, m2.merno as settl_mer_no,
            m3.name as mapto_mer_name, m3.merno as mapto_mer_no,
            f1.name as t20_fee_name, f1.notes as t20_fee_notes
        from pay_merch as m
        left join pay_merch as m2 on m.settl_merid = m2.uuid
        left join pay_merch as m3 on m.mapto_merid = m3.uuid
        left join pay_fee as f1 on f1.uuid = m.t20_fee
        where m.uuid = $1", [
            record.ref_uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next(new Error('未找到原商户记录'))
    res.locals.ref_record = result.rows[0]

    # 格式化为文本以供显示
    for obj in [record, res.locals.ref_record]
        if obj.unit_license_exp     # 日期和时间格式
            obj.unit_license_exp = obj.unit_license_exp.format('YYYY-MM-DD')

    # 将交易控制分解为独立的个体
    split_trctl = (record) ->
        return if not record.trctl
        record.trctl_trcodes = ''
        if record.trctl.trcodes
            for k, v of record.trctl.trcodes
                record.trctl_trcodes += constant.trctl_codes[k]
                record.trctl_trcodes += '、'

        for code in [ 't20', 't60', 'tz0' ]
            continue if not record.trctl["#{code}"]

            record["#{code}_time"] =
                (record.trctl["#{code}"].time_begin or '不限') + ' － ' +
                (record.trctl["#{code}"].time_end or '不限')
            record["#{code}_amt_per"] =
                (record.trctl["#{code}"].min_amt_per or '0.0') + ' 元 － ' +
                (record.trctl["#{code}"].max_amt_per or '0.0') + ' 元'
            record["#{code}_max_minute"] =
                (record.trctl["#{code}"].max_amt_minute or '0.0') + ' 元 / ' +
                (record.trctl["#{code}"].max_cnt_minute or '0') + ' 笔'
            record["#{code}_max_day"] =
                (record.trctl["#{code}"].max_amt_day or '0.0') + ' 元 / ' +
                (record.trctl["#{code}"].max_amt_day or '0') + ' 笔'

    split_trctl record
    split_trctl res.locals.ref_record
    res.render 'merch/check2', record: record


router.get '/check/3/:id', body_dropdown_items, (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '确认删除商户'

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 可以通过序号和 UUID 之一来查询
    key = if req.params.id.indexOf('-') > 0 then 'uuid' else 'serial'
    sql = "select m.*, u.realname as input_user, u2.realname as check_user
            from pay_merch_ex as m
            left join web_users as u on m.inputer = u.uuid
            left join web_users as u2 on m.checker = u2.uuid
            where m.#{key} = $1 and m.inputer <> $2 and action = 3"
    await client.query sql, [
        req.params.id, req.session.userinfo.uuid ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next(new Error('未找到商户记录'))
    record = result.rows[0]
    res.render 'merch/check3', record: record


check_precheck = (req, res, next) ->
    for k in [ 'serial', 'uuid', 'input_time' ]
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select m.*, u.realname as input_user,
            u2.realname as check_user
        from pay_merch_ex as m
        left join web_users as u on u.uuid = m.inputer
        left join web_users as u2 on u2.uuid = m.checker
        where m.serial = $1 and m.uuid = $2", [
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    if result.rows.length == 0
        return next new Error('''
            未查询到待复核记录，可能已经被其他用户复核并生效了，请检查''')
    record = result.rows[0]

    # 期间有可能其他用户进行了复核
    if record.checked and record.checker != req.session.userinfo.uuid
        return next new Error("""
            用户``#{record.check_user}''已经复核了此记录""")

    # 期间有可能用户修改了信息
    if record.input_time.isAfter req.body.input_time, 'second'
        return next new Error("""
            在您复核期间，用户``#{record.input_user}''再次修改了商户信息，请刷新
        """)

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    next()


router.post '/check/pass', check_precheck, (req, res, next) ->
    if not req.body.effect_time or req.body.effect_time.length == 0
        return next(new Error('请输入信息生效时间'))

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select action, inputer, ref_uuid from pay_merch_ex where uuid = $1", [
            req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    record = result.rows[0]

    # 删除时更新商户表的操作员和复核员，用于审计
    if record and parseInt(record.action) == 3
        await client.query \
            "update pay_merch set checker = $1, inputer = $2
                where uuid = $3 and checker <> $4 and inputer <> $5", [
                req.session.userinfo.uuid,
                record.inputer, record.ref_uuid
                req.session.userinfo.uuid,
                record.inputer
            ], defer err, result
        if err then done(client); return next(err)

    # 更新待复核记录，等待调度
    await client.query \
        "update pay_merch_ex set checked = true, checker = $1,
            check_time = CURRENT_TIMESTAMP, effect_time = $2,
            rejected = false, reject_reason = ''
            where serial = $3 and uuid = $4", [
            req.session.userinfo.uuid,
            req.body.effect_time,
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    # 增加调度器，在将来某个时间让商户生效
    jobs.sched_effect req.body.uuid, req.body.effect_time
    res.redirect '/merch'


router.post '/check/reject', check_precheck, (req, res, next) ->
    if not req.body.reject_reason or req.body.reject_reason.length == 0
        return next(new Error('请输入拒绝原因'))

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_merch_ex set checked = false, checker = $1,
            check_time = CURRENT_TIMESTAMP,
            rejected = true, reject_reason = $2
            where serial = $3 and uuid = $4", [
            req.session.userinfo.uuid,
            req.body.reject_reason,
            req.body.serial, req.body.uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.redirect '/merch'


router.post '/batch/check', (req, res, next) ->
    res.type('json')
    for k in ['records']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    records = JSON.parse req.body.records
    if records.length == 0
        return succ: true, redirect: '/merch'

    params = []
    params.push req.session.userinfo.uuid

    where_clause = "uuid in ("
    for item, index in records
        where_clause += "$#{index + 2}"
        if index < records.length - 1
            where_clause += ", "
        params.push item.uuid
    where_clause += ")"

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_merch_ex set checked = true, checker = $1,
            check_time = CURRENT_TIMESTAMP, effect_time = CURRENT_TIMESTAMP,
            rejected = false, reject_reason = ''
            where #{where_clause}", params, defer err, result
    if err then done(client); return next(err)
    done()

    # 增加调度器，在将来某个时间让商户生效
    for item in records
        jobs.sched_effect item.uuid, moment()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.post '/batch/disable', (req, res, next) ->
    res.type('json')
    for k in ['records']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    records = JSON.parse req.body.records
    if records.length == 0
        return succ: true, redirect: '/merch'

    params = []
    where_clause = "uuid in ("
    for item, index in records
        where_clause += "$#{index + 1}"
        if index < records.length - 1
            where_clause += ", "
        params.push item.uuid
    where_clause += ")"

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_merch set disabled = true
            where #{where_clause}", params, defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.post '/batch/enable', (req, res, next) ->
    res.type('json')
    for k in ['records']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    records = JSON.parse req.body.records
    if records.length == 0
        return succ: true, redirect: '/merch'

    params = []
    where_clause = "uuid in ("
    for item, index in records
        where_clause += "$#{index + 1}"
        if index < records.length - 1
            where_clause += ", "
        params.push item.uuid
    where_clause += ")"

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "update pay_merch set disabled = false
            where #{where_clause}", params, defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.post '/batch/delete', (req, res, next) ->
    res.type('json')
    for k in ['records']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    records = JSON.parse req.body.records

    uuids = "("
    for item, index in records
        uuids += "'#{item.uuid}'"
        if index < records.length - 1
            uuids += ", "
    uuids += ")"

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 如果有其它商户引用了此记录，则禁止删除
    await client.query \
        "select * from pay_merch where settl_merid in #{uuids} or
            mapto_merid in #{uuids}", defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("""
            记录中存在正被其它商户引用的记录，在解除引用关系之前不允许删除""")

    # 如果待复核表中有其它商户引用了此记录，则禁止删除
    await client.query \
        "select * from pay_merch_ex where settl_merid in #{uuids} or
            mapto_merid in #{uuids}", defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("""
            记录中存在正被待复核表中的其它商户引用的记录，在解除引用关系之前不允许删除""")

    # 如果有其他终端引用了此记录，则禁止删除
    await client.query \
        "select * from pay_terms where merid in #{uuids}", defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("""
            记录中存在正被终端引用的记录，在解除引用关系之前不允许删除""")

    # 如果此记录存在待复核的条目，则禁止删除
    await client.query "select * from pay_merch_ex
        where ref_uuid in #{uuids}", defer err, result
    if err then done(client); return next(err)
    if result.rows.length > 0
        done()
        return next new Error("""
            记录中有被用户修改后尚未生效的记录，在新信息生效前不允许删除""")

    await client.query "insert into pay_merch_ex (
            uuid,              inside,         chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            ref_serial,        ref_uuid,
            inputer,           input_time,     action
        ) ( select
            md5(random()::text || clock_timestamp()::text)::uuid::varchar,
            inside,            chnl,           merno,
            t20_fee,           trctl,          settl_merid,    mapto_merid,
            name,              contact,        telephone,      mobile,
            fax,               email,          address,
            unit_name,         unit_name_abbr, unit_english,     unit_english_abbr,
            unit_code,         unit_license,   unit_license_exp, unit_tax_code,
            unit_issuer,       unit_address,   unit_type,        unit_legal_name,
            unit_cred_type,    unit_cred_no,   unit_assets,      unit_scope,
            keys,              notes,
            serial,            uuid,
            $1,                CURRENT_TIMESTAMP, 3
            from pay_merch where uuid in #{uuids}
        )", [ req.session.userinfo.uuid ], defer err, result
    if err then done(client); return next(err)
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.post '/batch/delete_ex', (req, res, next) ->
    res.type('json')
    for k in ['records']
        return next new Error("请求缺少参数[#{k}]") if not req.body[k]

    records = JSON.parse req.body.records

    uuids = "("
    for item, index in records
        uuids += "'#{item.uuid}'"
        if index < records.length - 1
            uuids += ", "
    uuids += ")"

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query \
        "select ref_uuid from pay_merch_ex where
            inputer = $1 and uuid in #{uuids}", [
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)

    ref_uuids = "("
    for item, index in result.rows
        ref_uuids += "'#{item.ref_uuid}'"
        if index < records.length - 1
            ref_uuids += ", "
    ref_uuids += ")"

    await client.query 'begin'

    await client.query \
        "delete from pay_merch_ex where checked = false and
            inputer = $1 and uuid in #{uuids}", [
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)

    await client.query \
        "delete from pay_merch where shaded = true and
            inputer = $1 and uuid in #{ref_uuids}", [
            req.session.userinfo.uuid
        ], defer err, result
    if err then done(client); return next(err)

    await client.query 'commit'
    done()

    # 更新系统的通知修改时间，用户下次访问时，将重新查询通知消息
    req.app.set 'notify_modified_time', moment().format('x')
    res.json succ: true, redirect: '/merch'


router.get '/import', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '导入商户信息'
    res.render 'merch/import', record: {}


uploader = multer(dest: settings.upload_path)
router.post '/import', uploader.single('attach_file'), (req, res, next) ->
    for k in ['inside', 'chnl']
        if not req.body[k]
            return next(new Error("请求缺少参数[#{k}]"))
    if not req.file
        return next(new Error("请求缺少上传文件."))

    # 检查文件类型是否为文本文件
    magic = new mmm.Magic(mmm.MAGIC_MIME_TYPE)
    await magic.detectFile req.file.path, defer err, result
    if err
        fs.unlink req.file.path
        return next(new Error('不能检测文件类型，导入失败'))
    if result != 'text/plain'
        fs.unlink req.file.path
        return next(new Error('上传的文件格式非文本文件，无效'))

    # 将上传文件保存到指定的目录待后续处理
    if req.body.inside is 'true'
        dest_path = "#{settings.upload_path}/merch/#{req.body.chnl}/inner"
    else
        dest_path = "#{settings.upload_path}/merch/#{req.body.chnl}/outer"
    mkdirp.sync(dest_path)
    today = moment().format('YYYY-MM-DD')
    fs.rename req.file.path, "#{dest_path}/#{today}.txt"
    res.redirect '/merch'


router.get '/export', (req, res, next) ->
    res.locals.breadcrumbs.push 'title': '导出商户信息'
    res.render 'merch/export', record: {}


router.post '/export', (req, res, next) ->
    res.redirect '/merch'


# 可映射外联商户列表
router.all '/ajax/mapto_merids', (req, res, next) ->
    res.type('json')
    for k in ['chnl']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select uuid, merno, name, shaded
        from pay_merch where
            inside = false and chnl = $1 and disabled = false", [
            req.body.chnl
        ], defer err, result
    if err then done(client); return next(err)
    done()
    res.json succ: true, records: result.rows


# 查询交易控制信息
router.post '/ajax/trctl_info', (req, res, next) ->
    res.type('json')
    for k in ['trctl_uuid']
        return next(new Error("请求缺少参数[#{k}]")) if not req.body[k]

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    await client.query "select * from pay_trctl_t where uuid = $1", [
            req.body.trctl_uuid
        ], defer err, result
    if err then done(client); return next(err)
    done()

    record = result.rows[0]
    res.json succ: true, record: record
